﻿using LpbPrj.Client.Tools.Helper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using LpbPrj.Client;
using NPOI.SS.UserModel;

namespace Accounting
{
    internal class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("**********您好，欢迎使用成本及收入自动分析系统**********");

            List<PrjCostIncome> PrjCostIncomes = new List<PrjCostIncome>();

            //项目名称（编号+名称）
            List<string> prjectNames = JsonHelper.ToObject<List<string>>(File.ReadAllText(Path.Combine(Environment.CurrentDirectory, "Config", "Project.json")).Trim());
            prjectNames.ForEach(p => Console.WriteLine($"Excel读取项目：{p}"));
            List<string> prjectCodes = prjectNames.Select(p => p.Substring(0, 6)).ToList();
            Console.WriteLine($"******************************");
            //成本科目（编号+名称）
            List<string> CostNames = JsonHelper.ToObject<List<string>>(File.ReadAllText(Path.Combine(Environment.CurrentDirectory, "Config", "Cost.json")).Trim());
            CostNames.ForEach(p => Console.WriteLine($"Excel读取成本科目：{p}"));

            Console.WriteLine($"******************************");
            //收入科目（编号+名称）
            List<string> IncomeNames = JsonHelper.ToObject<List<string>>(File.ReadAllText(Path.Combine(Environment.CurrentDirectory, "Config", "Income.json")).Trim());
            IncomeNames.ForEach(p => Console.WriteLine($"Excel读取收入科目：{p}"));
            Console.WriteLine($"******************************");

            //所有Excel文件，每月一个，约定名称yyyyMM.xls
            List<string> fileNames = FileHelper.GetAllFiles(new DirectoryInfo(Path.Combine(Environment.CurrentDirectory, "Excel"))).Where(p => p.Extension.Equals(".xls") && p.Name.Length == 10).Select(p => p.Name).OrderBy(p => p).ToList();
            for (int i = 0; i < fileNames.Count; i++)
            {
                DateTime dateTime = new DateTime(System.Convert.ToInt32(fileNames[i].Substring(0, 4)), System.Convert.ToInt32(fileNames[i].Substring(4, 2)), 1);
                string fileName = Path.Combine(Environment.CurrentDirectory, "Excel", fileNames[i]);
                if (!File.Exists(fileName) || FileHelper.FileIsOpen(fileName)) continue;

                var dataRows = ExcelHelper.ExcelToDataTable(fileName, null, true, 6, 12).AsEnumerable().ToList();
                for (int j = 0; j < dataRows.Count; j++)
                {
                    var dataRow = dataRows[j];
                    try
                    {
                        //如果是项目开始处，则创建一个项目，后面开始读取项目的数据，直到下一个项目开始
                        var codeName = dataRow.Field<string>(1)?.Trim() + dataRow.Field<string>(2)?.Trim();
                        if (string.IsNullOrEmpty(codeName)) continue;
                        if (string.IsNullOrEmpty(dataRow.Field<string>(1)?.Trim())) continue;
                        if (string.IsNullOrEmpty(dataRow.Field<string>(2)?.Trim())) continue;


                        if (prjectNames.Any(u => u.Contains(dataRow.Field<string>(1)?.Trim())) && (!prjectNames.Contains(codeName)))
                        {
                            Console.ForegroundColor = ConsoleColor.Red;
                            Console.WriteLine($"{fileNames[i]}中，行{j + 8}：{codeName} 与Project和ExcelProject中项目名称和Excel中不一致，请修改Config目录下的Project.json和ExcelProject.json");
                            Console.ForegroundColor = ConsoleColor.White;

                            Console.WriteLine("**********发生错误，请关闭程序**********");
                            Console.ReadKey();
                        }
                        if (prjectNames.Contains(codeName))
                        {
                            PrjCostIncomes.Add(new PrjCostIncome { PrjCode = codeName.Substring(0, 6), Project = codeName, DateTime = dateTime });
                            Console.ForegroundColor = ConsoleColor.DarkYellow;
                            Console.WriteLine($"添加项目{j + 8}：{codeName}");
                            Console.ForegroundColor = ConsoleColor.White;
                            continue;
                        }
                        //成本科目
                        if (CostNames.Contains(codeName) || CostNames.Any(u => u.Contains(dataRow.Field<string>(1)?.Trim())))
                        {
                            var amount = (dataRow.Field<string>(5)?.Trim() + dataRow.Field<string>(6)?.Trim()).String2Double();
                            //如果是库存商品1405，使用本期的借方-本期发生的贷方
                            if (codeName.Contains("1405"))
                            {
                                //amount = (dataRow.Field<string>(5 + 3)?.Trim() + dataRow.Field<string>(6 + 3)?.Trim()).String2Double();
                                var amountSub = (dataRow.Field<string>(7)?.Trim()).String2Double();
                                Console.ForegroundColor = ConsoleColor.Green;
                                Console.WriteLine($"成本科目{j + 8}：{PrjCostIncomes[PrjCostIncomes.Count - 1].Project}---增加{codeName}借方： {amount}");
                                Console.WriteLine($"成本科目{j + 8}：{PrjCostIncomes[PrjCostIncomes.Count - 1].Project}---增加{codeName}贷方： {amountSub}");
                                Console.ForegroundColor = ConsoleColor.White;
                                amount = amount - amountSub;
                            }

                            if (!PrjCostIncomes[PrjCostIncomes.Count - 1].CostDict.Keys.Contains(codeName))
                            {
                                PrjCostIncomes[PrjCostIncomes.Count - 1].CostDict.Add(codeName, 0);
                            }
                            PrjCostIncomes[PrjCostIncomes.Count - 1].CostDict[codeName] = PrjCostIncomes[PrjCostIncomes.Count - 1].CostDict[codeName] + amount;
                            PrjCostIncomes[PrjCostIncomes.Count - 1].CostTotalCount += amount;

                            Console.ForegroundColor = ConsoleColor.Green;
                            Console.WriteLine($"成本科目{j + 8}：{PrjCostIncomes[PrjCostIncomes.Count - 1].Project}---增加{codeName}： {amount}");
                            Console.ForegroundColor = ConsoleColor.White;
                            continue;
                        }

                        //收入科目
                        if (IncomeNames.Contains(codeName) || IncomeNames.Any(u => u.Contains(dataRow.Field<string>(1)?.Trim())))
                        {
                            var amount = (dataRow.Field<string>(7)?.Trim() + "").String2Double();
                            if (!PrjCostIncomes[PrjCostIncomes.Count - 1].IncomeDict.Keys.Contains(codeName))
                            {
                                PrjCostIncomes[PrjCostIncomes.Count - 1].IncomeDict.Add(codeName, 0);
                            }
                            PrjCostIncomes[PrjCostIncomes.Count - 1].IncomeDict[codeName] = PrjCostIncomes[PrjCostIncomes.Count - 1].IncomeDict[codeName] + amount;
                            PrjCostIncomes[PrjCostIncomes.Count - 1].IncomeTotalCount += amount;
                            Console.ForegroundColor = ConsoleColor.Red;
                            Console.WriteLine($"收入科目{j + 8}：{PrjCostIncomes[PrjCostIncomes.Count - 1].Project}---增加{codeName}： {amount}");
                            Console.ForegroundColor = ConsoleColor.White;
                            continue;
                        }

                        Console.WriteLine($"未处理行{j + 8}：{codeName}");
                    }
                    catch (Exception ex)
                    {
                        Console.ForegroundColor = ConsoleColor.Red;
                        Console.WriteLine(ex.Message);
                        Console.WriteLine(ex.StackTrace);
                        Console.ForegroundColor = ConsoleColor.White;

                        Console.WriteLine("**********发生错误，请关闭程序**********");
                        Console.ReadKey();
                    }
                }

                Console.WriteLine($"**********{fileNames[i]}读取完毕**********");
            }

            //所有销售类项目Excel文件，每月一个，约定名称yyyyMMmxz.xls
            List<string> fileMxzNames = FileHelper.GetAllFiles(new DirectoryInfo(Path.Combine(Environment.CurrentDirectory, "Excel"))).Where(p => p.Extension.Equals(".xls") && p.Name.Length == 13 && p.Name.Contains("mxz")).Select(p => p.Name).OrderBy(p => p).ToList();
            for (int i = 0; i < fileMxzNames.Count; i++)
            {
                DateTime dateTime = new DateTime(System.Convert.ToInt32(fileMxzNames[i].Substring(0, 4)), System.Convert.ToInt32(fileMxzNames[i].Substring(4, 2)), 1);
                string fileName = Path.Combine(Environment.CurrentDirectory, "Excel", fileMxzNames[i]);
                if (!File.Exists(fileName) || FileHelper.FileIsOpen(fileName)) continue;

                var dataRows = ExcelHelper.ExcelToDataTable(fileName, null, true, 6, 12).AsEnumerable().ToList();
                for (int j = 0; j < dataRows.Count; j++)
                {
                    var dataRow = dataRows[j];
                    bool nextRow = false;
                    try
                    {
                        //读取项目编号
                        var prjName = dataRow.Field<string>(5)?.Trim() + dataRow.Field<string>(6)?.Trim();
                        //之前的表中不包含这个项目，但是需要统计
                        for (int k = 0; k < prjectNames.Count; k++)
                        {
                            if (prjName.Contains(prjectNames[k].Substring(0, 6)) && (!PrjCostIncomes.Select(p => p.PrjCode).Contains(prjectNames[k].Substring(0, 6))))
                            {
                                PrjCostIncomes.Add(new PrjCostIncome { PrjCode = prjectNames[k].Substring(0, 6), Project = prjectNames[k], DateTime = dateTime });
                                Console.ForegroundColor = ConsoleColor.DarkYellow;
                                Console.WriteLine($"添加项目{j + 8}：{prjectNames[k]}");
                                Console.ForegroundColor = ConsoleColor.White;
                                nextRow = true;
                                break;
                            }
                        }
                        if (nextRow) continue;

                        //之前的表中已经包含了这个项目
                        for (int k = 0; k < PrjCostIncomes.Count; k++)
                        {
                            if (prjName.Contains(PrjCostIncomes[k].PrjCode) && PrjCostIncomes[k].DateTime.Date == dateTime.Date)
                            {
                                //成本科目
                                var amount = (dataRow.Field<string>(7)?.Trim()).String2Double();
                                if (!PrjCostIncomes[k].CostDict.Keys.Contains("1405库存商品"))
                                {
                                    PrjCostIncomes[k].CostDict.Add("1405库存商品", 0);
                                }
                                PrjCostIncomes[k].CostDict["1405库存商品"] = PrjCostIncomes[k].CostDict["1405库存商品"] + amount;
                                PrjCostIncomes[k].CostTotalCount += amount;
                                Console.ForegroundColor = ConsoleColor.Yellow;
                                Console.WriteLine($"明细账表格{j + 8}：{PrjCostIncomes[k].Project}---增加1405库存商品： {amount}");
                                Console.ForegroundColor = ConsoleColor.White;
                                nextRow = true;
                                break;
                            }
                        }
                        if (nextRow) continue;
                    }
                    catch (Exception ex)
                    {
                        Console.ForegroundColor = ConsoleColor.Red;
                        Console.WriteLine(ex.Message);
                        Console.ForegroundColor = ConsoleColor.White;

                        Console.WriteLine("**********发生错误，请关闭程序**********");
                        Console.ReadKey();
                    }
                }

                Console.WriteLine($"**********{fileMxzNames[i]}读取完毕**********");
            }

            //统计的项目名称（编号+名称）
            List<string> ExcelPrjectNames = JsonHelper.ToObject<List<string>>(File.ReadAllText(Path.Combine(Environment.CurrentDirectory, "Config", "ExcelProject.json")).Trim());
            ExcelPrjectNames.ForEach(p => Console.WriteLine($"要统计项目：{p}"));
            Console.WriteLine($"******************************");
            //统计的成本科目（编号+名称）
            List<SubjectDict> ExcelCostNames = JsonHelper.ToObject<List<SubjectDict>>(File.ReadAllText(Path.Combine(Environment.CurrentDirectory, "Config", "ExcelCost.json")).Trim());
            ExcelCostNames.ForEach(p => Console.WriteLine($"要统计成本科目：{p.Code + p.Name}"));
            Console.WriteLine($"******************************");
            //统计的收入科目（编号+名称）
            List<SubjectDict> ExcelIncomeNames = JsonHelper.ToObject<List<SubjectDict>>(File.ReadAllText(Path.Combine(Environment.CurrentDirectory, "Config", "ExcelIncome.json")).Trim());
            ExcelIncomeNames.ForEach(p => Console.WriteLine($"要统计收入科目：{p.Code + p.Name}"));
            Console.WriteLine($"******************************");

            //写入Excel
            if (true)
            {
                IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();

                var excelStyle = new ExcelStyle
                {
                    Font = workbook.CreateFont(),
                    CellStyle = workbook.CreateCellStyle(),
                    HeaderCellStyle = workbook.CreateCellStyle(),
                    DateStyle = workbook.CreateCellStyle()
                };
                excelStyle.DateStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy-MM-dd");
                var sheet = workbook.CreateSheet($"{fileNames.FirstOrDefault().Substring(0, 4)}-{fileNames.LastOrDefault().Substring(0, 4)}");

                ICellStyle style = workbook.CreateCellStyle();
                style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index;
                //style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index;
                style.FillPattern = FillPattern.SolidForeground;

                //基本信息
                {
                    var columnHeaderIndex = 0;
                    var rowTitle = sheet.CreateRow(0);
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("项目");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("科目");

                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("01月成本");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("02月成本");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("03月成本");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("04月成本");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("05月成本");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("06月成本");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("07月成本");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("08月成本");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("09月成本");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("10月成本");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("11月成本");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("12月成本");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("成本");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("成本占比");

                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("01月收入");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("02月收入");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("03月收入");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("04月收入");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("05月收入");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("06月收入");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("07月收入");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("08月收入");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("09月收入");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("10月收入");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("11月收入");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("12月收入");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("收入");

                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("01月利润");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("02月利润");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("03月利润");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("04月利润");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("05月利润");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("06月利润");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("07月利润");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("08月利润");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("09月利润");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("10月利润");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("11月利润");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("12月利润");
                    rowTitle.CreateCell(columnHeaderIndex++).SetCellValue("利润");

                }

                //填充数据
                {
                    var rowIndex = 1;
                    foreach (var prjectName in ExcelPrjectNames)
                    {
                        //if (prjectName.Contains("000029"))
                        //{
                        //    Console.ForegroundColor = ConsoleColor.White;
                        //}

                        var prjCostIncomes = PrjCostIncomes.Where(p => p.Project.Contains(prjectName)).ToList();

                        var CostTotalCount = prjCostIncomes.Select(p => p.CostTotalCount).ToList().Sum();
                        var IncomeTotalCount = prjCostIncomes.Select(p => p.IncomeTotalCount).ToList().Sum();

                        var costMonthCount01 = prjCostIncomes.Where(p => p.DateTime.Month == 01).Select(p => p.CostTotalCount).ToList().Sum();
                        var costMonthCount02 = prjCostIncomes.Where(p => p.DateTime.Month == 02).Select(p => p.CostTotalCount).ToList().Sum();
                        var costMonthCount03 = prjCostIncomes.Where(p => p.DateTime.Month == 03).Select(p => p.CostTotalCount).ToList().Sum();
                        var costMonthCount04 = prjCostIncomes.Where(p => p.DateTime.Month == 04).Select(p => p.CostTotalCount).ToList().Sum();
                        var costMonthCount05 = prjCostIncomes.Where(p => p.DateTime.Month == 05).Select(p => p.CostTotalCount).ToList().Sum();
                        var costMonthCount06 = prjCostIncomes.Where(p => p.DateTime.Month == 06).Select(p => p.CostTotalCount).ToList().Sum();
                        var costMonthCount07 = prjCostIncomes.Where(p => p.DateTime.Month == 07).Select(p => p.CostTotalCount).ToList().Sum();
                        var costMonthCount08 = prjCostIncomes.Where(p => p.DateTime.Month == 08).Select(p => p.CostTotalCount).ToList().Sum();
                        var costMonthCount09 = prjCostIncomes.Where(p => p.DateTime.Month == 09).Select(p => p.CostTotalCount).ToList().Sum();
                        var costMonthCount10 = prjCostIncomes.Where(p => p.DateTime.Month == 10).Select(p => p.CostTotalCount).ToList().Sum();
                        var costMonthCount11 = prjCostIncomes.Where(p => p.DateTime.Month == 11).Select(p => p.CostTotalCount).ToList().Sum();
                        var costMonthCount12 = prjCostIncomes.Where(p => p.DateTime.Month == 12).Select(p => p.CostTotalCount).ToList().Sum();
                        var incomeMonthCount01 = prjCostIncomes.Where(p => p.DateTime.Month == 01).Select(p => p.IncomeTotalCount).ToList().Sum();
                        var incomeMonthCount02 = prjCostIncomes.Where(p => p.DateTime.Month == 02).Select(p => p.IncomeTotalCount).ToList().Sum();
                        var incomeMonthCount03 = prjCostIncomes.Where(p => p.DateTime.Month == 03).Select(p => p.IncomeTotalCount).ToList().Sum();
                        var incomeMonthCount04 = prjCostIncomes.Where(p => p.DateTime.Month == 04).Select(p => p.IncomeTotalCount).ToList().Sum();
                        var incomeMonthCount05 = prjCostIncomes.Where(p => p.DateTime.Month == 05).Select(p => p.IncomeTotalCount).ToList().Sum();
                        var incomeMonthCount06 = prjCostIncomes.Where(p => p.DateTime.Month == 06).Select(p => p.IncomeTotalCount).ToList().Sum();
                        var incomeMonthCount07 = prjCostIncomes.Where(p => p.DateTime.Month == 07).Select(p => p.IncomeTotalCount).ToList().Sum();
                        var incomeMonthCount08 = prjCostIncomes.Where(p => p.DateTime.Month == 08).Select(p => p.IncomeTotalCount).ToList().Sum();
                        var incomeMonthCount09 = prjCostIncomes.Where(p => p.DateTime.Month == 09).Select(p => p.IncomeTotalCount).ToList().Sum();
                        var incomeMonthCount10 = prjCostIncomes.Where(p => p.DateTime.Month == 10).Select(p => p.IncomeTotalCount).ToList().Sum();
                        var incomeMonthCount11 = prjCostIncomes.Where(p => p.DateTime.Month == 11).Select(p => p.IncomeTotalCount).ToList().Sum();
                        var incomeMonthCount12 = prjCostIncomes.Where(p => p.DateTime.Month == 12).Select(p => p.IncomeTotalCount).ToList().Sum();

                        //项目及总数
                        {
                            var rowContent = sheet.CreateRow(rowIndex++);
                            var columnIndex = 0;
                            rowContent.CreateCell(columnIndex++, CellType.String).SetCellValue(prjectName);                         //项目
                            rowContent.CreateCell(columnIndex++, CellType.String);                                                  //科目

                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(costMonthCount01);                      //01月成本
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(costMonthCount02);                      //02月成本
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(costMonthCount03);                      //03月成本
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(costMonthCount04);                      //04月成本
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(costMonthCount05);                      //05月成本
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(costMonthCount06);                      //06月成本
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(costMonthCount07);                      //07月成本
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(costMonthCount08);                      //08月成本
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(costMonthCount09);                      //09月成本
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(costMonthCount10);                      //10月成本
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(costMonthCount11);                      //11月成本
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(costMonthCount12);                      //12月成本
                            var cell0 = rowContent.CreateCell(columnIndex++, CellType.Numeric);                                         //成本
                            cell0.SetCellValue(CostTotalCount);
                            cell0.CellStyle = style;
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);                                                     //成本占比

                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount01);                    //01月收入
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount02);                    //02月收入
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount03);                    //03月收入
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount04);                    //04月收入
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount05);                    //05月收入
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount06);                    //06月收入
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount07);                    //07月收入
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount08);                    //08月收入
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount09);                    //09月收入
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount10);                    //10月收入
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount11);                    //11月收入
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount12);                    //12月收入
                            var cell1 = rowContent.CreateCell(columnIndex++, CellType.Numeric);                                         //收入
                            cell1.SetCellValue(IncomeTotalCount);
                            cell1.CellStyle = style;

                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount01 - costMonthCount01); //01月利润
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount02 - costMonthCount02); //02月利润
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount03 - costMonthCount03); //03月利润
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount04 - costMonthCount04); //04月利润
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount05 - costMonthCount05); //05月利润
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount06 - costMonthCount06); //06月利润
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount07 - costMonthCount07); //07月利润
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount08 - costMonthCount08); //08月利润
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount09 - costMonthCount09); //09月利润
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount10 - costMonthCount10); //10月利润
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount11 - costMonthCount11); //11月利润
                            rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(incomeMonthCount12 - costMonthCount12); //12月利润
                            var cell2 = rowContent.CreateCell(columnIndex++, CellType.Numeric);                                         //利润
                            cell2.SetCellValue(IncomeTotalCount - CostTotalCount);
                            cell2.CellStyle = style;
                        }

                        //成本
                        for (int j = 0; j < ExcelCostNames.Count; j++)
                        {
                            var rowContent = sheet.CreateRow(rowIndex++);
                            var columnIndex = 0;

                            rowContent.CreateCell(columnIndex++, CellType.String);
                            rowContent.CreateCell(columnIndex++, CellType.String).SetCellValue(ExcelCostNames[j].ToString());

                            //1月
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 01).SelectMany(p => p.CostDict).Where(p => p.Key.Contains(ExcelCostNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }

                            //2月
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 02).SelectMany(p => p.CostDict).Where(p => p.Key.Contains(ExcelCostNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }

                            //3月
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 03).SelectMany(p => p.CostDict).Where(p => p.Key.Contains(ExcelCostNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }

                            //4月
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 04).SelectMany(p => p.CostDict).Where(p => p.Key.Contains(ExcelCostNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }

                            //5月
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 05).SelectMany(p => p.CostDict).Where(p => p.Key.Contains(ExcelCostNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }

                            //6月
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 06).SelectMany(p => p.CostDict).Where(p => p.Key.Contains(ExcelCostNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }

                            //7月
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 07).SelectMany(p => p.CostDict).Where(p => p.Key.Contains(ExcelCostNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }

                            //8月
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 08).SelectMany(p => p.CostDict).Where(p => p.Key.Contains(ExcelCostNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }

                            //9月
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 09).SelectMany(p => p.CostDict).Where(p => p.Key.Contains(ExcelCostNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }

                            //10月
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 10).SelectMany(p => p.CostDict).Where(p => p.Key.Contains(ExcelCostNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }

                            //11月
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 11).SelectMany(p => p.CostDict).Where(p => p.Key.Contains(ExcelCostNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }

                            //12月
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 12).SelectMany(p => p.CostDict).Where(p => p.Key.Contains(ExcelCostNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }
                            //总数
                            {
                                var count = prjCostIncomes.SelectMany(p => p.CostDict).Where(p => p.Key.Contains(ExcelCostNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                                if (CostTotalCount > 0.0)
                                {
                                    var precent = Math.Round(count / CostTotalCount, 2, MidpointRounding.AwayFromZero);
                                    rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(precent);
                                }
                                else
                                {
                                    rowContent.CreateCell(columnIndex++, CellType.Numeric);
                                }
                            }

                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);


                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);

                        }

                        //收入
                        for (int j = 0; j < ExcelIncomeNames.Count; j++)
                        {
                            var rowContent = sheet.CreateRow(rowIndex++);
                            var columnIndex = 0;

                            rowContent.CreateCell(columnIndex++, CellType.String);
                            rowContent.CreateCell(columnIndex++, CellType.String).SetCellValue(ExcelIncomeNames[j].ToString());

                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);

                            //1
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 01).SelectMany(p => p.IncomeDict).Where(p => p.Key.Contains(ExcelIncomeNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }
                            //2
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 02).SelectMany(p => p.IncomeDict).Where(p => p.Key.Contains(ExcelIncomeNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }
                            //3
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 03).SelectMany(p => p.IncomeDict).Where(p => p.Key.Contains(ExcelIncomeNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }
                            //4
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 04).SelectMany(p => p.IncomeDict).Where(p => p.Key.Contains(ExcelIncomeNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }
                            //5
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 05).SelectMany(p => p.IncomeDict).Where(p => p.Key.Contains(ExcelIncomeNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }
                            //6
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 06).SelectMany(p => p.IncomeDict).Where(p => p.Key.Contains(ExcelIncomeNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }
                            //7
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 07).SelectMany(p => p.IncomeDict).Where(p => p.Key.Contains(ExcelIncomeNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }
                            //8
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 08).SelectMany(p => p.IncomeDict).Where(p => p.Key.Contains(ExcelIncomeNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }
                            //9
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 09).SelectMany(p => p.IncomeDict).Where(p => p.Key.Contains(ExcelIncomeNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }
                            //10
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 10).SelectMany(p => p.IncomeDict).Where(p => p.Key.Contains(ExcelIncomeNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }
                            //11
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 11).SelectMany(p => p.IncomeDict).Where(p => p.Key.Contains(ExcelIncomeNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }
                            //12
                            {
                                var count = prjCostIncomes.Where(p => p.DateTime.Month == 12).SelectMany(p => p.IncomeDict).Where(p => p.Key.Contains(ExcelIncomeNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }
                            //总数
                            {
                                var count = prjCostIncomes.SelectMany(p => p.IncomeDict).Where(p => p.Key.Contains(ExcelIncomeNames[j].Code)).ToList().Select(p => p.Value).ToList().Sum();
                                rowContent.CreateCell(columnIndex++, CellType.Numeric).SetCellValue(count);
                            }

                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);
                            rowContent.CreateCell(columnIndex++, CellType.Numeric);

                        }
                    }
                }

                //写入到excel
                using (FileStream fs = new FileStream(Path.Combine(Environment.CurrentDirectory, "Excel", $"{fileNames.FirstOrDefault().Substring(0, 6)}-{fileNames.LastOrDefault().Substring(0, 6)}.xlsx"), FileMode.OpenOrCreate, FileAccess.ReadWrite))
                {
                    workbook.Write(fs);
                    workbook.Close();
                }

                var cur = PrjCostIncomes.Where(p => p.PrjCode.Equals("000029")).ToList();

                string json = JsonHelper.ToJson(PrjCostIncomes);
                File.WriteAllText(Path.Combine(Environment.CurrentDirectory, "Excel", $"{fileNames.FirstOrDefault().Substring(0, 6)}-{fileNames.LastOrDefault().Substring(0, 6)}.json"), json);
            }


            Console.WriteLine("**********谢谢使用成本及收入自动分析系统，按任意键退出**********");
            Console.ReadKey();
        }
    }

    /// <summary>
    /// 项目成本及收入
    /// </summary>
    public class PrjCostIncome
    {
        /// <summary>
        /// 项目编号
        /// </summary>
        public string PrjCode { get; set; }
        /// <summary>
        /// 项目名称
        /// </summary>
        public string Project { get; set; }

        /// <summary>
        /// 发生时间
        /// </summary>
        public DateTime DateTime { get; set; } = DateTime.Now.Date;

        /// <summary>
        /// 成本科目及对应的值
        /// 读取本期发生额借方数据
        /// </summary>
        public Dictionary<string, double> CostDict { get; set; } = new Dictionary<string, double>();
        /// <summary>
        /// 成本科目综合
        /// </summary>
        public double CostTotalCount { get; set; } = 0.0;

        /// <summary>
        /// 主营业务收入科目及对应的值
        /// 读取本期发生额贷方数据
        /// </summary>
        public Dictionary<string, double> IncomeDict { get; set; } = new Dictionary<string, double>();
        /// <summary>
        /// 收入科目综合
        /// </summary>
        public double IncomeTotalCount { get; set; } = 0.0;


    }


    /// <summary>
    /// 科目字典
    /// </summary>
    public class SubjectDict
    {
        /// <summary>
        /// 科目编号
        /// </summary>
        public string Code { get; set; }
        /// <summary>
        /// 科目名称
        /// </summary>
        public string Name { get; set; }

        public override string ToString()
        {
            return Code + Name;
        }
    }


}
